<?php

namespace app\models;

use app\librarys\DbCacheUtility;

/**
 * 供ApiController使用的model
 * @package app\models
 */
class Api
{
    /**
     * 获取商品列表数量
     * @param $institutionCode
     * @param string|null $goodsCode
     * @param string|null $goodsName
     * @param string|null $goodsCategoryCode
     * @param int|null $isNewest [0/1]
     * @param int|null $isHot [0/1]
     * @param int|null $integralFrom
     * @param int|null $integralTo
     * @param bool $isGroup 是否按group_code分组
     * @return false|null|int
     */
    public static function getGoodsListCountBySearch($institutionCode, $goodsCode = null, $goodsName = null, $goodsCategoryCode = null, $isNewest = null, $isHot = null, $integralFrom = null, $integralTo = null, $isGroup = false)
    {
        if ($institutionCode == null || $institutionCode == '') {
            return 0;
        }

        $filterSql = '';
        if (!empty($goodsCode)) {
            $filterSql = $filterSql . " AND t0.`code` LIKE :goods_code ";
        }
        if (!empty($goodsName)) {
            $filterSql = $filterSql . " AND t0.`name` LIKE :goods_name ";
        }
        if (!empty($goodsCategoryCode)) {
            $filterSql = $filterSql . " AND t1.category_code LIKE :goods_category_code ";
        }
        if (!empty($isNewest)) {
            $filterSql = $filterSql . " AND t0.is_newest = :is_newest ";
        }
        if (!empty($isHot)) {
            $filterSql = $filterSql . " AND t0.is_hot = :is_hot ";
        }
        if (!empty($integralFrom)) {
            $filterSql = $filterSql . " AND (t0.selling_price*t2.integral_rate)>=:integral_f ";
        }
        if (!empty($integralTo)) {
            $filterSql = $filterSql . " AND (t0.selling_price*t2.integral_rate)<=:integral_t ";
        }

        $tGoodsSql = "SELECT * FROM t_goods WHERE institution_code=:institution_code";
        if ($isGroup == true) {
            $tGoodsSql = $tGoodsSql . " GROUP BY group_code";
        }

        $cmd = \Yii::$app->db->createCommand("
            SELECT COUNT(1)
            FROM ($tGoodsSql) t0
            LEFT JOIN t_goods_category_goods t1 ON t1.institution_code=t0.institution_code AND t1.goods_code=t0.`code`
            LEFT JOIN t_institution t2 ON t2.`code`=t0.institution_code
            WHERE t0.is_enabled=1
            AND t0.is_shelved=1
            AND t0.is_activity=0
            AND t0.is_show=1
            $filterSql");
        $cmd->bindValue(':institution_code', $institutionCode);

        if (!empty($goodsCode)) {
            $cmd->bindValue(':goods_code', '%' . $goodsCode . '%');
        }
        if (!empty($goodsName)) {
            $cmd->bindValue(':goods_name', '%' . $goodsName . '%');
        }
        if (!empty($goodsCategoryCode)) {
            $cmd->bindValue(':goods_category_code', $goodsCategoryCode . '%');
        }
        if (!empty($isNewest)) {
            $cmd->bindValue(':is_newest', $isNewest);
        }
        if (!empty($isHot)) {
            $cmd->bindValue(':is_hot', $isHot);
        }
        if (!empty($integralFrom)) {
            $cmd->bindValue(':integral_f', $integralFrom);
        }
        if (!empty($integralTo)) {
            $cmd->bindValue(':integral_t', $integralTo);
        }

        $count = DbCacheUtility::queryScalar($cmd);
        return $count;
    }

    /**
     * 获取商品列表
     * @param $institutionCode
     * @param int $page
     * @param int $pageSize
     * @param string|null $goodsCode
     * @param string|null $goodsName
     * @param string|null $goodsCategoryCode
     * @param int|null $isNewest [0/1]
     * @param int|null $isHot [0/1]
     * @param int|null $integralFrom
     * @param int|null $integralTo
     * @param int|null $sort 1:按价格从小到大; 2:按价格从大到小; 3:按上架时间从新到旧; 4:按上架时间从旧到新
     * @param bool $isGroup 是否按group_code分组
     * @return array|null
     */
    public static function getGoodsListBySearch($institutionCode, $page = 1, $pageSize = 10, $goodsCode = null, $goodsName = null, $goodsCategoryCode = null, $isNewest = null, $isHot = null, $integralFrom = null, $integralTo = null, $sort = null, $isGroup = false)
    {
        if ($institutionCode == null || $institutionCode == '') {
            return [];
        }

        $filterSql = '';
        if (!empty($goodsCode)) {
            $filterSql = $filterSql . " AND t0.`code` LIKE :goods_code ";
        }
        if (!empty($goodsName)) {
            $filterSql = $filterSql . " AND t0.`name` LIKE :goods_name ";
        }
        if (!empty($goodsCategoryCode)) {
            $filterSql = $filterSql . " AND t1.category_code LIKE :goods_category_code ";
        }
        if (!empty($isNewest)) {
            $filterSql = $filterSql . " AND t0.is_newest = :is_newest ";
        }
        if (!empty($isHot)) {
            $filterSql = $filterSql . " AND t0.is_hot = :is_hot ";
        }
        if (!empty($integralFrom)) {
            $filterSql = $filterSql . " AND (t0.selling_price*t2.integral_rate)>=:integral_f ";
        }
        if (!empty($integralTo)) {
            $filterSql = $filterSql . " AND (t0.selling_price*t2.integral_rate)<=:integral_t ";
        }

        switch ($sort) {
            case 1:  // 按价格从小到大
                $filterSql = $filterSql . ' ORDER BY t0.selling_price ';
                break;
            case 2:  // 按价格从大到小
                $filterSql = $filterSql . ' ORDER BY t0.selling_price DESC ';
                break;
            case 3:  // 按上架时间从新到旧
                $filterSql = $filterSql . ' ORDER BY t0.shelve_datetime ';
                break;
            case 4:  // 按上架时间从旧到新
                $filterSql = $filterSql . ' ORDER BY t0.shelve_datetime DESC ';
                break;
        }

        $page = $page < 1 ? 1 : $page;
        $offset = ($page - 1) * $pageSize;

        $tGoodsSql = "SELECT * FROM t_goods WHERE institution_code=:institution_code";
        if ($isGroup == true) {
            $tGoodsSql = $tGoodsSql . " GROUP BY group_code";
        }

        $cmd = \Yii::$app->db->createCommand("
            SELECT t0.`code`, t0.`name`, t0.display_name, t0.group_code, t0.group_item_name, t0.model, t0.format, t0.brand, t0.selling_price, t0.selling_price*t2.integral_rate as selling_integral, t2.integral_unit, t0.shelve_datetime, t0.is_newest, t0.is_hot, t0.is_top, t0.is_trusteeship, t0.is_crossborder, t3.image_url AS goods_image
            FROM ($tGoodsSql) t0
            LEFT JOIN t_goods_category_goods t1 ON t1.institution_code=t0.institution_code AND t1.goods_code=t0.`code`
            LEFT JOIN t_institution t2 ON t2.`code`=t0.institution_code
            LEFT JOIN t_goods_image t3 ON t3.goods_code=t0.`code` AND t3.type=1
            WHERE t0.is_enabled=1
            AND t0.is_shelved=1
            AND t0.is_activity=0
            AND t0.is_show=1
            $filterSql
            LIMIT :offset, :limit");
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':offset', $offset);
        $cmd->bindValue(':limit', $pageSize);

        if (!empty($goodsCode)) {
            $cmd->bindValue(':goods_code', '%' . $goodsCode . '%');
        }
        if (!empty($goodsName)) {
            $cmd->bindValue(':goods_name', '%' . $goodsName . '%');
        }
        if (!empty($goodsCategoryCode)) {
            $cmd->bindValue(':goods_category_code', $goodsCategoryCode . '%');
        }
        if (!empty($isNewest)) {
            $cmd->bindValue(':is_newest', $isNewest);
        }
        if (!empty($isHot)) {
            $cmd->bindValue(':is_hot', $isHot);
        }
        if (!empty($integralFrom)) {
            $cmd->bindValue(':integral_f', $integralFrom);
        }
        if (!empty($integralTo)) {
            $cmd->bindValue(':integral_t', $integralTo);
        }

        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 获取商品详情
     * @param $institutionCode
     * @param $goodsCode
     * @return array|false|null
     */
    public static function getGoodsDetail($institutionCode, $goodsCode)
    {
        $sql = "
        SELECT t.`code`, t.`name`, t.display_name, t.group_code, t.group_item_name, t.model, t.format, t.brand, t.selling_price, t.selling_price*t5.integral_rate as selling_integral, t5.integral_unit, t6.detail_html, t.is_enabled, t.is_shelved, t.is_show, t.is_newest, t.is_hot, t.is_hot, t.is_top, t.is_activity, t.is_trusteeship, t.is_crossborder, t4.image_url as goods_image,c.code as category_code,c.name as category_name
        FROM t_goods t
        left join t_goods_category_goods cg on cg.goods_code = t.code and cg.institution_code = t.institution_code
        left join t_goods_category c on c.code = cg.category_code and c.institution_code = cg.institution_code
        left join t_goods_image t4 on t4.goods_code=t.`code` and t4.type=1
        left join t_institution t5 on t5.`code`=t.institution_code
        left join t_goods_detail_html t6 on t6.`code`=t.`code` and t6.institution_code=t.institution_code
        where t.institution_code=:institution_code and t.code=:goods_code ";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':goods_code', $goodsCode);
        $result = $cmd->queryOne();
        if ($result != false) {
            $cmd = \Yii::$app->db->createCommand('SELECT image_url FROM t_goods_image WHERE goods_code=:goodsCode');
            $cmd->bindValue(':goodsCode', $goodsCode);
            $result['goods_image_list'] = $cmd->queryColumn();
            return $result;
        } else {
            return null;
        }
    }

    /**
     * 获取某个用户的积分记录数量
     * @param string $userId
     * @return int
     */
    public static function getIntegralRecordCountByUser($userId)
    {
        $sql = "SELECT COUNT(1) FROM t_user_integral_record WHERE user_id=:user_id";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':user_id', $userId);

        $item = $cmd->queryScalar();
        return $item;
    }

    /**
     * 获取某个用户的积分记录列表
     * @param string $userId
     * @param int $page
     * @param int $pageSize
     * @return array
     */
    public static function getIntegralRecordListByUser($userId, $page, $pageSize = 10)
    {

        $sql = "
            SELECT id, integral, record_type, create_datetime, remark FROM t_user_integral_record
            WHERE user_id=:user_id
            ORDER BY create_datetime DESC
            LIMIT :offset, :limit";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':user_id', $userId);
        $cmd->bindValue(':offset', ($page - 1) * $pageSize);
        $cmd->bindValue(':limit', $pageSize);
        $items = $cmd->queryAll();

        return $items;
    }

}